package ca.ualberta.cs.CLRDB;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.HashSet;

import uk.ac.liv.c3connector.AnnotationModel;

import ca.ualberta.cs.BibTex.Author;
import ca.ualberta.cs.BibTex.BibTex;
import ca.ualberta.cs.DB2ExceptionHandling.DB2SqlException;

public class BibTexManagement extends EntityRelManagement {
	
	public BibTexManagement(){
		mainTableName = "BibTex";
	}
	
	public void initialize(Statement statement) throws SQLException{
		
		try{
			statement.executeUpdate("DROP TABLE BibTex");
		}catch(Exception e){}
		
		statement.executeUpdate("CREATE TABLE BibTex ( " +
				"ID INTEGER GENERATED ALWAYS AS " +
					"IDENTITY (START WITH 1 INCREMENT BY 1) NOT NULL, " +
				"bibtex VARCHAR(1000), " +
				"title VARCHAR(100) NOT NULL, " +
				"DOI VARCHAR(50), " +
				"ISSN VARCHAR(50), " +
				"ISBN VARCHAR(50), " +
				"CONSTRAINT id_uniq PRIMARY KEY(ID) " +
				") IN CLRSPACE INDEX IN CLRSPACE");
		numOfCols = 6;
		statement.execute("SET INTEGRITY FOR BibTex OFF");
		
		statement.executeUpdate("ALTER TABLE BibTex "+
				"ADD COLUMN pseudoDOI GENERATED ALWAYS AS " +
				"(CASE WHEN DOI IS NULL THEN ID END) ");
		
		statement.executeUpdate("SET INTEGRITY FOR BibTex IMMEDIATE CHECKED FORCE GENERATED");
		
		statement.executeUpdate("create unique index u_bib_doi on BibTex(pseudoDOI, DOI)");

		statement.executeUpdate("ALTER TABLE BibTex " +
   				"ADD COLUMN " +
   					"abstract VARCHAR(5000) "+
   				"ADD COLUMN " + "uploadPath VARCHAR(300) " +
   				"ADD COLUMN " + "authors VARCHAR(300) " +
   				"ALTER COLUMN title SET DATA TYPE VARCHAR(300) "
   				);  		
		
		numOfCols++;

	}
	
	
	   /**
	    * @param bib
	    * @param con
	    * @return -1: does not exists, -2: an exception, bibtexId: exists
	    */
	   public int bibtexExists(BibTex bib, Connection con){
		   
		   boolean innerConnection = false;
		   
		   if(con == null){
			   con = DatabaseController.connectToDB();
			   innerConnection = true;
		   }
		   
		   int bibId = -1; //does not exist
		   	
		   try{
			   
			   Statement statement = con.createStatement();
			   
			   //find bibId (bib is not null, otherwise this method was not called)
			   
			   //FIXME: may DB have the resource but not the DOI. in any of the following, the presence should be checked and if exists: update to a more complete bibtex info
			   if( bib.getDOI() != null ){
				   ResultSet res = statement.executeQuery("SELECT ID FROM BibTex WHERE LOWER(DOI) = '" + bib.getDOI().toLowerCase() + "'");
				   if( res.next() ){
					   bibId = res.getInt("ID");					   
				   }
			   }
			   if(bibId == -1) //bib.getDoi==null or db does not have that doi
				   if(bib.getTitle() != null){
					   ResultSet res = statement.executeQuery("SELECT ID, ISSN, ISBN, bibtex FROM BibTex WHERE LOWER(title) = '" + bib.getTitle().toLowerCase() + "'");
					   while( res.next() ){
						   if(res.getString("ISSN")!=null && bib.getISSN()!= null && !res.getString("ISSN").equalsIgnoreCase(bib.getISSN()))
							   continue;
						   if(res.getString("ISBN")!=null && bib.getISBN()!= null && !res.getString("ISBN").equalsIgnoreCase(bib.getISBN()))
							   continue;
						   
						   ArrayList<Author> dbauthors = new BibTex(res.getString("bibtex")).getAuthors();
						   if(dbauthors.size()!=0 && bib.getAuthors().size()!=0){
							   if(dbauthors.size() != bib.getAuthors().size())
								   continue;
							   boolean same = true;
							   for(int i = 0 ; i < dbauthors.size() ; i++ )
								   if(!dbauthors.get(i).equals(bib.getAuthors().get(i))){
									   same = false;
									   break;
								   }
							   if(!same)
								   continue; 
						   }
						   bibId = res.getInt("ID");
					   }					  
				   }
			   
			   if(bibId == -1)
				   return -1;
			   
			   statement.close();
			   if(innerConnection){
				   con.commit();
				   con.close();				   
			   }
		   
		   } catch (SQLException e) {
			   DB2SqlException.printSqlErrorMsg(e,con,innerConnection);
				return -2;
			} catch (Exception e) {			
				e.printStackTrace();
				DB2SqlException.closeConnection(con, innerConnection);
				return -2;
			} 
		   
		   return bibId;
	   }
	   
	   public BibTex updateBibtex(Integer bibtexId, BibTex newBib, Connection con){
		   
		   System.out.println("------Somebody updating bibtex for id:"+bibtexId+"-----\n");
		   
		   boolean innerConnection = false;
		   
		   if(con == null){
			   con = DatabaseController.connectToDB();
			   innerConnection = true;
		   }
		   		   
		   try{
			   Statement statement = con.createStatement();
			   
			   ResultSet result = statement.executeQuery("SELECT * FROM BibTex WHERE ID = "+bibtexId);
			   if(!result.next()) //does not happen
				   return newBib;
		
			   String DOI = null;
			   String ISSN = null;
			   String ISBN = null;
			   HashSet<String> tags = null; 
			   String abstr = null;
//			   String title = null;
			   
			   String oldBibtex = result.getString("bibtex");
			   String oldAbstr = result.getString("abstract");
			   BibTex oldBib = new BibTex(oldBibtex);
			   
			   if(oldBibtex.equals(newBib.getTheWhole())){
				   System.out.println("nothing to update - bibtex");
				   return newBib;
			   }
			   
//			   String oldTitle 
			   
			   //set basic info, and fill newBib with info from oldBib when oldBib has more info
			   
			   /*//title
			   if(oldBib.getTitle() == null && newBib.getTitle() != null)
				   title = newBib.getTitle();
			   else
				   newBib.setTitle(oldBib.getTitle());*/
			   
			   //DOI:
			   if(oldBib.getDOI()==null && newBib.getDOI() != null)
				   DOI = newBib.getDOI();
			   else
				   newBib.setDoi(oldBib.getDOI()); //even if null it works
			   
			   //ISSN
			   if(oldBib.getISSN()==null && newBib.getISSN() != null)
				   ISSN = newBib.getISSN();
			   else
				   newBib.setISSN(oldBib.getISSN());
			   
			   //ISBN
			   if(oldBib.getISBN()==null && newBib.getISBN() != null)
				   ISBN = newBib.getISBN();
			   else
				   newBib.setISBN(oldBib.getISBN());
			   
			   //keywords
			   if(oldBib.getKeywords()==null && newBib.getKeywords() != null )
				   tags = newBib.getKeywords();
			   else if(oldBib.getKeywords()!=null && newBib.getKeywords()!=null
					   && oldBib.keywordsToString().length() < newBib.keywordsToString().length() ){
				   tags = newBib.getKeywords();
			   }
			   else{
				   //tags are already inserted, when old bibtex was inserted
				   newBib.setKeywords(oldBib.keywordsToString()); //just update the bibtex string
			   }
			   //abstract
			   if((oldAbstr==null || oldAbstr.length()==0) && newBib.getAbstract()!=null)
				   abstr = newBib.getAbstract();
			   else if((oldAbstr!=null && oldAbstr.length()!=0) && newBib.getAbstract()!=null
					   && oldAbstr.length() < newBib.getAbstract().length()){
				   abstr = newBib.getAbstract();
			   }
			   else{
				   newBib.setAbstract(oldAbstr);
				   abstr = oldAbstr;
			   }
			   		   
			   
			   String updatedBib = newBib.toString();
			   			   
			   String keys = "(" + (DOI!=null?"DOI,":"") + (ISSN!=null? "ISSN, ":"") + (ISBN!=null? "ISBN, ":"") + (abstr!=null? "abstract, ":"") + "bibtex)"; 
			   String vals = "('" + (DOI!=null?DOI+"','":"") + (ISSN!=null?ISSN+"', '":"") + (ISBN!=null?ISBN+"', '":"") + (abstr!=null?abstr+"', '":"") + updatedBib +"')";
			   
			   /*System.out.println("--------- update bibtex ----------");
			   System.out.println("\nkeys:"+keys);
			   System.out.println("vals:"+vals);
			   System.out.println("----------------------------------");*/
			   
			   statement.executeUpdate("UPDATE BibTex SET "+keys+" = "+vals+" WHERE ID = "+bibtexId);
			   
			   //taggings are related to resources, so, they are added when resource is added (in addResource..() series)
			   
			   statement.close();
			   if(innerConnection){
				   con.commit();
				   con.close();				   
			   }
			   
		   }catch (SQLException e) {
				DB2SqlException.printSqlErrorMsg(e,con,innerConnection);
				return newBib;
			} catch (Exception e) {			
				e.printStackTrace();
				DB2SqlException.closeConnection(con, innerConnection);
				return newBib;
			}
			return newBib;
	   }
	   
	   /**
	    * 
	    * @param url
	    * @param DOI
	    * @param keywords
	    * @param con
	    * @return resourceId
	    */
	   public int updateResourceBibtex(String url, String DOI, String keywords, Connection con){
		   
		   if( DOI == null && keywords == null){
			   return -1; //FIXME, should search for url and return it. not done now, since I think we never call it will both be null!
		   }
		   
		   int resourceId = -1;
		   boolean innerConnection = false;
		   
		   if(con == null){
			   con = DatabaseController.connectToDB();
			   innerConnection = true;
		   }
		   		   
		   try{
			   Statement statement = con.createStatement();
			   
			   ResultSet res = statement.executeQuery("SELECT r.bibtexId, b.bibtex, r.ID FROM BibTex b, Resource r WHERE r.url = '"+url+"'" +
		   			"AND r.bibtexId IS NOT NULL AND r.bibtexId = b.ID");
			   if(!res.next())
				   return -1; //no bib for this url
			   		//FIXME, should return url's resourceId, but I don't think it happens! since this is called after we know there is bibtex
			   
			   int bibId = res.getInt("bibtexId");
			   resourceId = res.getInt("ID");
			   
			   //check doi to be unique:
			   if(DOI != null){
				   Statement statementDoi = con.createStatement();
				   ResultSet resDoi = statementDoi.executeQuery("SELECT id from Bibtex WHERE DOI = '"+DOI+"'");
				   if(resDoi.next()){
					   int doiId = resDoi.getInt("ID");
					   if(doiId != bibId){
						   //wow! why?! either user has entered bad info (title, authors) or we have sometime extracted them wrong
						   statementDoi.close();
						   statement.close();
						   if(innerConnection){
							   con.commit();
							   con.close();				   
						   }
						   return resourceId;
					   }
				   }
				   statementDoi.close();				   
			   }
			   
			   String oldBibtex = res.getString("bibtex");
			   BibTex bib = new BibTex(oldBibtex);
			   if(DOI != null )
				   bib.setDoi(DOI);
			   
//			   Author firstAuthor = null;
			   HashSet<String> tags = null; 
			   if(keywords != null){
				   bib.setKeywords(keywords);
//				   firstAuthor = bib.getAuthors().get(0);
				   tags = bib.getKeywords();
			   }
			   String updatedBib = bib.toString();
			   			   
			   String keys = "(" + (DOI!=null?"DOI,":"") + "bibtex)"; //not both of doi and keywords are null
			   String vals = "(" + (DOI!=null?"'"+DOI+"','":"'") + updatedBib +"')";
			   
			   System.out.println("\n---------- update bibtex knowing url: -----------");
			   System.out.println("keys:"+keys);
			   System.out.println("vals:"+vals);
			   System.out.println("--------------------------------------------------");
			   
			   statement.executeUpdate("UPDATE BibTex SET "+keys+" = "+vals+" WHERE ID = "+bibId);
			   
			   //now add taggings:
			   if(tags != null && tags.size() != 0){
				   /*int personId = ResourceManagement.authoringMgmt.findThisAuthorsIdByResource(resourceId, firstAuthor, statement);
				   ResourceManagement.tagsMgmt.addTaggings(tags, resourceId, personId, 0, null, statement);*/
				   ResourceManagement.tagsMgmt.addKeywords(tags, bibId, statement);
			   }
			   
			   statement.close();
			   if(innerConnection){
				   con.commit();
				   con.close();				   
			   }
			   
		   }catch (SQLException e) {
				DB2SqlException.printSqlErrorMsg(e, con, innerConnection);	
				return -1;
			} catch (Exception e) {			
				e.printStackTrace();
				DB2SqlException.closeConnection(con, innerConnection);
				return -1;
			}
		   	return resourceId;		 
	   }
	   
	   /**
	    * in order to stop repeatedly asking people to insert bibs (which may be incompatible too), 
	    * we may check whether a specific url has a distinguishable bib (by DOI)
	    * if DOI is not yet set, we may only ask user to enter DOI not a full bib
	    * we may also ask for keywords if the current bib doesn't have keywords (then the bibtex text should be updated with added 
	    * keywords to prevent adding keywords several times (difficult to be found by checking Tagging) 
	    * and we assume authors and title are provided if a bib is provided
	    * @param url
	    * @return String, '0' if no bib available , otherwise 'ab': a and b can be 0 or 1. a: 0 means no DOI in DB, 1: DOI available. b: talks about existence of keywords
	    */
	   public String urlHasDoiAndKeywords(String url, Connection con){
		   		   
		   String doiE = "0";
		   String keywordsE = "0";
		   
		   boolean innerConnection = false;
		   
		   if(con == null){
			   con = DatabaseController.connectToDB();
			   innerConnection = true;
		   }
		   		   
		   try{
			   Statement statement = con.createStatement();
			   
			   ResultSet res = statement.executeQuery("SELECT bibtexId FROM Resource WHERE url = '"+url+"'" +
			   		" AND bibtexId IS NOT NULL");
			   if(!res.next())
				   return "0"; //no bib for this url
			   
			   //do{
				   int bibId = res.getInt("bibtexId");
				   Statement st2 = con.createStatement();
				   ResultSet res2 = st2.executeQuery("SELECT DOI,bibtex FROM BibTex WHERE ID = "+bibId);
				   res2.next();
				   if(res2.getString("DOI") != null) 
					   doiE = "1";		
				   HashSet<String> keywords = new BibTex(res2.getString("bibtex")).getKeywords();
				   if(keywords != null && keywords.size() != 0)
					   keywordsE = "1";
				   
			   //}while(res.next()); //only one row with this url

			   statement.close();
			   if(innerConnection){
				   con.commit();
				   con.close();				   
			   }
			   
		   }catch (SQLException e) {
			   DB2SqlException.printSqlErrorMsg(e,con,innerConnection);
				return doiE + keywordsE;
			} catch (Exception e) {			
				e.printStackTrace();
				DB2SqlException.closeConnection(con, innerConnection);
				return doiE + keywordsE;
			}    
		   return doiE + keywordsE;
	   }
	   
	   public Integer insertNewBibTex(BibTex bib, Statement statement) throws SQLException{
		   
		   if(bib == null)
			   return -1;
		   
		   String keys = "(title," + (bib.getDOI()!=null? "doi, ":"") + 
  			(bib.getISSN()!=null? "ISSN, ":"") + (bib.getISBN()!=null? "ISBN, ":"") + 
  				 (bib.getAbstract()!=null?"abstract, ":"") + "bibtex)";

		   try{
			   if(bib.getTheWhole() == null){
				   bib.setTheWhole();
				   bib.getTheWhole().replace("'", "`");
			   }
		   }
		   catch(Exception e){
			   System.out.println("error setting the whole for a bibtex using toString");
			   System.out.println("**** caught:");
			   e.printStackTrace();
			   System.out.println("*****");
			   bib.setTheWhole("");
		   }
		   
			String values = "('"+bib.getTitle().replace("'", "`")+(bib.getDOI()!=null?"', '"+bib.getDOI():"") +
			  (bib.getISSN()!=null?"', '"+bib.getISSN():"") + (bib.getISBN()!=null?"', '"+bib.getISBN():"") +
			  (bib.getAbstract()!=null?"', '"+bib.getAbstract():"") +	
			  	"', '"+bib.getTheWhole().replace("'", "`")+"')";
			
			/*System.out.println(keys);
			System.out.println();
			System.out.println(values);
			*/
			Integer bibtexId = null;
			try{
				statement.executeUpdate("INSERT INTO BibTex "+keys+" VALUES " +values , Statement.RETURN_GENERATED_KEYS);
				ResultSet bibId = statement.getGeneratedKeys();
				bibId.next();
				bibtexId = bibId.getInt(1);
			}catch(SQLException e){ //unique constraint disturbed
				if(e.getErrorCode() != -803 )
				  throw e;
				else{
				 ResultSet re = statement.executeQuery("SELECT ID FROM BibTex WHERE title = '"+bib.getTitle()+"'");
				 if(re.next())
					  bibtexId = re.getInt("ID");
				}
			}
			return bibtexId;
		   
	   }
	   
	   public void insertNewBibtexInformation(AnnotationModel am, String username){
		   		
	   		HashMap<String,String> attr = ContentSerializer.parseAttributes(am.getAnnotationBody());
	   		
	   		if(attr.containsKey("needed")){
	   			String url = null;
	   			if(attr.containsKey("url"))
   					url = attr.get("url");
	   			
	   			if(url == null){
	   				//do nothing
	   				return;
	   			}
	   			
	   			if(url.startsWith("file:/"))
	   				url = username + ":" + url;
	   			
	   			//if(attr.get("needed").equals("0")){ //full bibtex was needed, so bibtex is given
	   			if(!attr.get("needed").equals("11")){  //sth was needed, sth is probably given!		   				
	   				
	   				BibTex bib = null;
	   				String title = null;
	   				String authors = null;
	   				String keywords = null;
	   				String doi = null;
	   				
	   				if(attr.containsKey("keywords"))
	   					keywords = attr.get("keywords");
	   				if(attr.containsKey("doi"))
	   					doi = attr.get("doi");	   				
	   				if(attr.containsKey("bibtitle"))
	   					title = attr.get("bibtitle");
	   				if(attr.containsKey("bibauthors"))
	   					authors = attr.get("bibauthors");
	   				
	   				
	   					if( (title != null && title.length() != 0) && (authors != null && authors.length() != 0)){
	   						bib = new BibTex(null);
	   						bib.setTitle(title);
	   						bib.setAuthors(authors);
	   					}
	   					if( doi != null || keywords != null ){
	   						if(bib != null){ //already title and authors are filled
	   							bib.setDoi(doi);
	   							bib.setKeywords(keywords);
	   						}
	   						else{ //only update DOI and/or keywords. if these are given but title, authors are not it means that we had them in db (otherwise user had to enter it as a required field)
	   							updateResourceBibtex(url, doi, keywords, null);
	   							return;
	   						}	   						
	   					}
	   					if(bib != null){
   							try{
   								String bibtex = bib.toString();
   							}catch(Exception e){
   			   					System.out.println("\n Wrong bibtex");	   			   					
   			   					System.out.println("discarded \n");
   			   					return;
   			   				}
   							DatabaseController.resourceMgmt.getReadingManagement().pageAccessed(bib, null, url, username);
   						}	   				
	   			}
	   		}
	   		
	   }
	   		
}
